HELP = """
功能：从包含Rohs文档的文件夹获取信息（厂商或料号）并更新到RoHs表格
文件夹路径和表格路径自己修改，表格数据区域，列索引按实际修改
# todo 根据总bom（厂商信息）和仪器bom（子系统bom）生成RoHs bom
"""
import xlwings as xw
import os
import re
import shutil

app = xw.App(visible=False, add_book=False)


def find_rohs_doc(RoHs_Folder, SN, vendor=None):
    """
        在documents文件夹查找文件名包含SN或vendor的文档
    """
    documents = os.listdir(RoHs_Folder)
    for d in documents:
        if SN in d:
            return d
        elif vendor is not None and vendor in d:
            return d


def update_RoHs_xlsx(Rohs_xlsx_path, RoHs_Folder):
    wb = app.books.open(Rohs_xlsx_path)
    sht = wb.sheets('STRU-ROHS')
    Range = sht.range("A2:H397") # 表格数据区域
    rows = Range.rows
    num = 0
    for row in rows:
        for j,column in enumerate(row.columns):
            if j==1: # 物料号SN所在的列
                SN = str(int(column.value))
            if j==4: # 厂商信息所在的列
                vendor = column.value
            if j==6: # 是否有RoHs所在的列
                haveROHS = column.value
                if haveROHS!="Y":
                    document_path = find_rohs_doc(RoHs_Folder, SN, vendor)
                    if document_path is not None:
                        print("找到ROHS", document_path)
                        num+=1
                        if True: # 方便调试
                            shutil.copy2(os.path.join(RoHs_Folder, document_path), os.path.dirname(Rohs_xlsx_path)) 
                            column.value = "Y"
                            row.columns[7].value = document_path
    wb.save()
    wb.close()
    print("新增", num)


# Rohs_xlsx_path = r"E:\蓝海梦\vitros450\STRU-SVN\3-Objective Evidence\ODBC-STRU-T9\ROHS\STRU Rohs list.xlsx"
# RoHs_Folder1 = r"E:\蓝海梦\vitros450\项目资料\BOM\ODBC标准件RoHS符合性证明资料汇总"
# update_RoHs_xlsx(Rohs_xlsx_path, RoHs_Folder1)
# print("done")
# 上面的代码是STRU的

def get_vendor(BOM_path):
    '''
        从BOM表里面扣出 物料SN对应的厂商 
    '''
    wb = app.books.open(BOM_path)
    sht = wb.sheets('Sheet1')
    sn_rng = sht.range("E3:E2256")
    vendor_rng = sht.range("AJ3:AJ2256")
    risk_rng = sht.range("AH3:AH2256")
    ret = {}
    for i, row in enumerate(sn_rng):
        sn = re.search(r'\d+', str(row.value)).group()
        ret[sn] = {"vendor":vendor_rng[i].value, 'risk':risk_rng[i].value}
    with open('vendors.txt', 'w', encoding='utf8') as f:
        f.write(str(ret))
    return ret


def get_RoHS_filename(RoHS_folders):
    '''
        返回所有rohs文件名字
    '''
    ret = []
    for folder in RoHS_folders:
        ret += [os.path.join(folder, name) for name in os.listdir(folder)]
    with open('filenames.txt', 'w', encoding='utf8') as f:
        f.write(str(ret))
    return ret


def have_rohs(filenames, SN, vendor=None):
    for name in filenames:
        if str(SN) in name:
            return name
        elif vendor is not None:
            if vendor in name:
                return name
            elif '市' in vendor and vendor.replace("市",'') in name:
                return name
    

def update_ODBC_BOM(ODBC_BOM_path, vendors, filenames):
    wb = app.books.open(ODBC_BOM_path)
    wb.activate()
    sheets_count = wb.sheets.count
    for i in range(sheets_count):
        print(f"修改第{i+1}个sheet")
        sht = wb.sheets(i+1)
        sht.activate()
        first_row_count = sht.range("A1").expand('right').count
        xw.Range((1,first_row_count+1)).value = 'have_rohs'
        xw.Range((1,first_row_count+2)).value = 'filename'
        xw.Range((1,first_row_count+3)).value = 'risk'
        xw.Range((1,first_row_count+4)).value = 'vendor'


        sn_rng = sht.range("B2").expand('down')
        for i, row in enumerate(sn_rng):
            sn = re.search(r'\d+', str(row.value))
            if not sn:
                print("无效的sn:", row.value)
                continue
            sn = sn.group()
            vendor, risk = None, None
            if sn in vendors:
                vendor = vendors[sn]['vendor']
                if vendor is None:
                    vendor = 'Bom里没维护厂商'
                risk = vendors[sn]['risk']
            rohs = have_rohs(filenames, sn, vendor)
            yes_no = 'Y' if rohs else 'N'
            xw.Range((i+2,first_row_count+1), (i+2,first_row_count+1)).value = yes_no
            xw.Range((i+2,first_row_count+2), (i+2,first_row_count+2)).value = os.path.basename(rohs)
            xw.Range((i+2,first_row_count+3), (i+2,first_row_count+3)).value = risk
            xw.Range((i+2,first_row_count+4), (i+2,first_row_count+4)).value = vendor
            if rohs:
                document_path = rohs
                save_dir = os.path.join(os.path.dirname(ODBC_BOM_path), 'rohs_files')
                os.makedirs(save_dir, exist_ok=True)
                
                if not os.path.exists(os.path.join(os.path.dirname(ODBC_BOM_path), os.path.basename(document_path))):
                    print(f"复制文件{rohs}到{os.path.dirname(ODBC_BOM_path)}")
                    shutil.copy2(document_path, save_dir) 

    wb.save()
    wb.close()
    print('报存成功')


BOM_path = r"E:\python\quaero\RoHS\ODBC 产成品按BOM展开 - 可筛选 20240319.xlsx"  # 包含厂商信息的总BOM
rohs_folder1 = r"E:\vitros450\RoHS\ODBC标准件RoHS符合性证明资料汇总(1)"
rohs_folder2 = r"E:\vitros450\RoHS\ODBC非标准件RoHS符合性证明资料汇总(1)"  # rohs文件保存的地方
# ODBC_BOM_path = r"E:\python\quaero\RoHS\BOM-LVCC 45001016~45001018 June 17th ENGLISH.xlsx"  # 要查找是否有RoHs的ODBC BOM
ODBC_BOM_path = r"C:\Users\KS00620\Desktop\BOM-ODBC V16 ENGLISH.xlsx"

vendors = get_vendor(BOM_path)
filenames = get_RoHS_filename([rohs_folder1, rohs_folder2])
update_ODBC_BOM(ODBC_BOM_path, vendors, filenames)

app.quit()

